﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CRMFramework;
using CRMEntity;
using System.Data;

namespace CRMBiz
{
    public static class HistoryHandler
    {
        public static bool AddHistory(HistoryInfo historydata)
        {
            bool flag = false;
            try
            {
                string sbSql = string.Empty;
                var para = new[] {
                    DBhelper.SetParameter("@appID", historydata.appID),
                    DBhelper.SetParameter("@contactID", historydata.contactID),
                    DBhelper.SetParameter("@HistoryDate", historydata.historyDate),
                    DBhelper.SetParameter("@HistoryTime", historydata.historyTime),
                    DBhelper.SetParameter("@Action", historydata.action),
                    DBhelper.SetParameter("@Operator", historydata.oprator),
                    DBhelper.SetParameter("@Description", historydata.description),
                };
                if (historydata.appID > 0)
                    sbSql = "insert into crm_history(app_id, HistoryDate, HistoryTime, Action, Operator, Description) values(@appID,STR_TO_DATE(@HistoryDate,'%m/%d/%Y'), @HistoryTime, @Action, @Operator, @Description)";
                else if (historydata.contactID > 0)
                    sbSql = "insert into crm_contacthistory(ContactID, HistoryDate, HistoryTime, Action, Operator, Description) values(@contactID,STR_TO_DATE(@HistoryDate,'%m/%d/%Y'), @HistoryTime, @Action, @Operator, @Description)";
                else
                    return flag;

                DBhelper.ExecuteNonQuery(sbSql, para);
                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        public static bool EditHistory(HistoryInfo historydata)
        {
            bool flag = false;
            try
            {
                string sbSql = string.Empty;
                var para = new[] {
                    DBhelper.SetParameter("@HistoryID",  historydata.historyID),
                    DBhelper.SetParameter("@HistoryDate", historydata.historyDate),
                    DBhelper.SetParameter("@HistoryTime", historydata.historyTime),
                    DBhelper.SetParameter("@Action", historydata.action),
                    DBhelper.SetParameter("@Operator", historydata.oprator),
                    DBhelper.SetParameter("@Description", historydata.description),
                };
                if (historydata.appID > 0)
                    sbSql = "Update crm_history SET HistoryDate=STR_TO_DATE(@HistoryDate,'%m/%d/%Y'), HistoryTime=@HistoryTime, Action=@Action, Operator=@Operator, Description=@Description where HistoryID=@HistoryID";
                else
                    sbSql = "Update crm_contacthistory SET HistoryDate=STR_TO_DATE(@HistoryDate,'%m/%d/%Y'), HistoryTime=@HistoryTime, Action=@Action, Operator=@Operator, Description=@Description where HistoryID=@HistoryID";

                DBhelper.ExecuteNonQuery(sbSql, para);
                flag = true;
            }
            catch (Exception)
            {
                throw;
            }
            return flag;
        }

        public static DataSet SelectAllHistoryByType(string historyType, int ID, int pageindex, int pagesize)
        {
            DataSet ds;
            try
            {
                string strquery = string.Empty;
                var para = new[] {
                    DBhelper.SetParameter("@ID",  ID),
                    DBhelper.SetParameter("@pagesize", pagesize),
                    DBhelper.SetParameter("@limit", (pageindex - 1) * pagesize)
                  };
                if (historyType == "Application")
                    strquery = " SELECT *,(select count(*) from crm_history where app_id=@ID) as Total from crm_history where app_id=@ID ORDER by HistoryDate desc,HistoryTime DESC LIMIT @limit, @pagesize";
                else
                    strquery = " SELECT *,(select count(*) from crm_contacthistory where ContactID=@ID) as Total from crm_contacthistory where ContactID=@ID ORDER by HistoryDate desc,HistoryTime DESC LIMIT @limit, @pagesize";

                ds = DBhelper.ExecuteDataset(strquery, para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        public static DataSet SelectHistoryByID(string historyType, int historyID)
        {
            DataSet ds;
            try
            {
                string strquery = string.Empty;
                var para = new[] {
                    DBhelper.SetParameter("@historyID",  historyID)
                  };
                if (historyType == "Application")
                    strquery = "SELECT * from crm_history where HistoryID=@historyID";
                else
                    strquery = "SELECT * from crm_contacthistory where HistoryID=@historyID";

                ds = DBhelper.ExecuteDataset(strquery, para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }
        public static DataSet GetHistoryNotFieldChange(string historyType, int pageindex, int pagesize, int ID)
        {
            DataSet ds;
            try
            {
                string strquery = string.Empty;
                var para = new[] {
                    DBhelper.SetParameter("@ID",  ID),
                    DBhelper.SetParameter("@pagesize", pagesize),
                    DBhelper.SetParameter("@limit", (pageindex - 1) * pagesize)
                  };
                if (historyType == "Application")
                    strquery = " SELECT *,(select count(*) from crm_history where app_id=@ID AND action!='Field Changed(System)' AND action!='Online submitted') as Total from crm_history where app_id=@ID AND action!='Field Changed(System)' AND action!='Online submitted' ORDER by HistoryDate desc,HistoryTime DESC LIMIT @limit, @pagesize";
                else
                    strquery = " SELECT *,(select count(*) from crm_contacthistory where ContactID=@ID AND action!='Field Changed(System)' AND action!='Online submitted') as Total from crm_contacthistory where ContactID=@ID AND action!='Field Changed(System)' AND action!='Online submitted' ORDER by HistoryDate desc,HistoryTime DESC LIMIT @limit, @pagesize";
                ds = DBhelper.ExecuteDataset(strquery, para);
            }
            catch (Exception)
            {
                throw;
            }
            return ds;
        }

        public static DataTable GetAllApplicationHistoryByAction(int app_id, string Action)
        {
            DataTable dt;
            try
            {
                string sbSql = string.Empty;
                var para = new[] {
                    DBhelper.SetParameter("@app_id",  app_id),
                    DBhelper.SetParameter("@Action", Action),
                };
                if (Action == "Except System")
                    sbSql = "SELECT HistoryID,HistoryDate,HistoryTime,Action,Operator,Description from crm_history where ACTION <> 'Field Changed(System)' and app_id=@app_id order by HistoryDate desc,HistoryTime desc";
                else if (Action == "ALL")
                    sbSql = "SELECT HistoryID,HistoryDate,HistoryTime,Action,Operator,Description from crm_history where app_id=@app_id order by HistoryDate desc,HistoryTime desc";
                else
                    sbSql = "SELECT HistoryID,HistoryDate,HistoryTime,Action,Operator,Description from crm_history where app_id=@app_id and Action=@Action order by HistoryDate desc,HistoryTime desc";
                dt = DBhelper.ExecuteDataset(sbSql, para).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        //Select current user login history:
        public static DataSet SelectLoginHistory(int ContactID)
        {
            DataSet ds;
            try
            {
                string strquery = string.Empty;
                var para = new[] {
                    DBhelper.SetParameter("@ContactID",  ContactID)
                };
                strquery = "SELECT LoginDate,LoginTime,Display_Name,Email_Address,LoginIP,LoginBrowser,ComesFrom from client_loginhistory where CurrentContact_id=@ContactID order by LoginDate DESC";
                ds = DBhelper.ExecuteDataset(strquery, para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }
    }
}
